package com.sam;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.mysql.jdbc.Driver;

public class MemberDAO {
        public static Connection conn = null;

        public static Connection getConnection() {
                if (conn == null) {
                        try {
                                DriverManager.registerDriver(new Driver());
                                conn = DriverManager.getConnection(
                                                "jdbc:mysql://j.snpy.org/j102u17", "j102u17", "j102u17.989");
                        } catch (SQLException e) {
                                // TODO Auto-generated catch block
                                e.printStackTrace();
                        }
                }
                return conn;

        }

        public List<Member> getAll() {
                List<Member> members = new ArrayList<Member>();
                try {
                        Statement stmt = getConnection().createStatement();
                        ResultSet rs = stmt.executeQuery("select * from users");
                        while (rs.next()) {
                                String userid = rs.getString("userid");
                                String username = rs.getString("username");
                                String nickname = rs.getString("nickname");
                                String password = rs.getString("password");
                                String email = rs.getString("email");
                                String status = rs.getString("status");
                                String vid = rs.getString("vid");
                                Member member = new Member(userid, username, nickname, password, email, status, vid);
                                members.add(member);
                        }
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }
                return members;
        }

        public int delete(Member m) {
        	String sql = "update users set status=?  where userid=?";
        	int rowCount = 0;
        	try {
        		PreparedStatement pstmt = getConnection().prepareStatement(sql);
        		pstmt.setString(5, "Y");
        		pstmt.setString(6, m.getUserid());
        		rowCount = pstmt.executeUpdate();
                } catch (SQLException e) {
                	// TODO Auto-generated catch block
                	e.printStackTrace();
                }
                return rowCount;
        }
        
        public int add(Member m){
        	String sql = "insert into users(userid,username,nickname,password,email,status) values (?,?,?,?,?,?)";
        	int rowCount = 0;
        	try {
        		PreparedStatement pstmt = getConnection().prepareStatement(sql);
        		pstmt.setString(1, m.getUserid());
        		pstmt.setString(2, m.getUsername());
        		pstmt.setString(3, m.getNickname());
        		pstmt.setString(4, m.getPassword());
        		pstmt.setString(5, m.getEmail());
        		pstmt.setString(6, " ");
        		rowCount = pstmt.executeUpdate();
        		System.out.println(rowCount);
        	} catch (SQLException e) {
        		// TODO Auto-generated catch block
        		e.printStackTrace();
        	}
        	return rowCount;
        }
        
        public int update(Member m){
        	String sql = "update users set username=?,nickname=?,password=?,email=?,status=?  where userid=?";
        	int rowCount = 0;
        	try {
        		PreparedStatement pstmt = getConnection().prepareStatement(sql);
        		pstmt.setString(1, m.getUsername());
        		pstmt.setString(2, m.getNickname());
        		pstmt.setString(3, m.getPassword());
        		pstmt.setString(4, m.getEmail());
        		pstmt.setString(5, m.getStatus());
        		pstmt.setString(6, m.getUserid());
        		rowCount = pstmt.executeUpdate();
                } catch (SQLException e) {
                	// TODO Auto-generated catch block
                	e.printStackTrace();
                }
                return rowCount;
        }

        public Member getMemberById(String userid){
        	Member member = null;
        	String sql = "select * from users where userid=?";
        	
        	try {
        		PreparedStatement pstmt = getConnection().prepareStatement(sql);
        		pstmt.setString(1, userid);
        		ResultSet rs = pstmt.executeQuery();
        		if (rs.next()){
        			member = new Member(rs.getString("userid")
                                				, rs.getString("username")
                                                , rs.getString("nickname")
                                                , rs.getString("password")
                                                , rs.getString("email")
                                                , rs.getString("status"));
        		}
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }
                return member;
        }
        
}
